package com.handu.luna.core.domain.goods;

import java.math.BigDecimal;
import java.util.Collection;
import java.util.List;
import java.util.Set;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.handu.luna.core.domain.BaseRepository;
import com.handu.luna.core.domain.support.GoodsGroupInfo;

public interface GoodsRepository extends JpaRepository<Goods, Long>,BaseRepository<Goods>, JpaSpecificationExecutor<Goods> {

	/**
	 * 查询全部商品
	 */
	@Query("select b from Goods b where deleted=false  order by createdDate desc  ")
	Page<Goods> findAll(Pageable pageable);
	
	/**
	 * 查询回收站商品
	 **/
	@Query("select b from Goods b where deleted=true  order by modifiedDate desc  ")
	Page<Goods> findTrash(Pageable pageable);
	
	/**
	 * 查询未删除商品
	 **/
	@Query("select b from Goods b where b.id =:id and deleted=false ")
	List<Goods> findUnTrash(@Param("id") Long id);
	
	/**
	 * 还原回收站商品
	 **/
	@Modifying
	@Query("update Goods b  set b.deleted = false where b.id =:id")
	void updateTrash(@Param("id") Long id);

	/**
	 * 通过goodsSN查询
	 * @return
	 */
	@Query("select g from Goods g where g.goodsSN = :goodsSN")
	List<Goods> findByGoodsSN(@Param("goodsSN") String goodsSN);
	

	
	/**
	 * 更新商品的上架状态
	 */
	@Modifying
	@Query("update Goods b  set b.displayed = :displayed where b.id =:id")
	void updateDisplayedTrue(@Param("displayed") Boolean displayed,@Param("id") Long id);
	
	/**
	 * 根据商品ID的集合更新商品副标题
	 * @param ids
	 * @param title
	 */
	@Modifying
	@Query("update Goods b set b.title = :title where b.id in :ids")
	void updateTitleByGoodsId(@Param("ids")Collection<Long> ids,@Param("title")String title);
	
	/**
	 * 根据商品的品牌 和 类型更新商品副标题 
	 * 注意：根据类型修改副标题的时候，需要将类型以及下属分类对应商品的标题都修改 
	 * select c.id from Catagory c   where c.id = :id or c.parentId = :id
	 * @param brandId
	 * @param categoryId
	 * @param title
	 */
	@Modifying
	@Query("update Goods b set b.title = :title where b.brandId = :brandId and b.categoryId in :categoryIds")
	void updateTitleByBrandAndType(@Param("brandId" )long brandId,@Param("categoryIds")Collection<Long> categoryIds,@Param("title")String title);
	
	/**
	 * 根据商品品牌修改商品副标题
	 * @param brandId
	 * @param title
	 */
	@Modifying
	@Query("update Goods b set b.title = :title where b.brandId = :brandId")
	void updateTitleByBrandId(@Param("brandId" )long brandId,@Param("title")String title);
	
	/**
	 * 修改商品副标题
	 * @param brandId
	 * @param title
	 */
	@Modifying
	@Query("update Goods b set b.title = :title ")
	void updateTitle(@Param("title")String title);
	
	/**
	 * 根据商品类型修改商品副标题
	 * 注意：根据类型修改副标题的时候，需要将类型以及下属分类对应商品的标题都修改 
	 * @param categoryIds
	 * @param title
	 */
	@Modifying
	@Query("update Goods b set b.title = :title where  b.categoryId in :categoryIds")
	void updateTitleByCatagoryIds(@Param("categoryIds")Collection<Long> categoryIds,@Param("title")String title);
	/**
	 * 当删除商品分类的时候，判断该分类下是否有商品，若有，则不允许删除
	 * @param id
	 * @return
	 */
	@Query("from Goods g where g.categoryId=:id and deleted=false")
	List<Goods> findAll(@Param("id") Long id);
	
	//计算分类的数量
	@Query("select count(*) from Goods g where g.deleted = false and g.categoryId = :id")
	 long  getNumByCatId(@Param("id") long id);
	
	@Query("select count(*) from Goods g where g.deleted = false and g.displayed = true and g.categoryId = :id")
	 long  getNumByCatId4Portal(@Param("id") long id);
	
	@Query("select NEW com.handu.luna.core.domain.support.GoodsGroupInfo(g.brandId ,'品牌','', count(g)) from Goods g where g.id in ?1 and g.deleted = false and g.displayed = true group by g.brandId")
	List<GoodsGroupInfo> groupByBrand4Portal(List<Long> goodsIdList);
	
	@Query("select NEW com.handu.luna.core.domain.support.GoodsGroupInfo(g.categoryId ,'','', count(g)) from Goods g where g.deleted = false and g.displayed = true group by g.categoryId")
	List<GoodsGroupInfo> groupByCategory4Portal();
	
	@Query("select NEW com.handu.luna.core.domain.support.GoodsGroupInfo(g.categoryId ,'','', count(g)) from Goods g where g.deleted = false and g.displayed = true and g.id in :ids group by g.categoryId")
	List<GoodsGroupInfo> groupByCategory4Portal(@Param("ids")Collection<Long> ids);
	
	@Query("select NEW com.handu.luna.core.domain.support.GoodsGroupInfo(g.categoryId ,'','', count(g)) from Goods g where g.deleted = false and g.displayed = true and g.categoryId in :categoryIds and g.id in :ids group by g.categoryId")
	List<GoodsGroupInfo> groupByCategory4Portal(@Param("categoryIds")List<Long> categoryIdList,@Param("ids")Collection<Long> ids);
	
	@Query("select count(g) from Goods g where g.deleted = false and g.displayed = true and g.categoryId in :categoryIds and g.id in :ids")
	long findByCategory4Portal(@Param("categoryIds")List<Long> categoryIdList,@Param("ids")Collection<Long> ids);

    List<Goods> findAll(Specification<Goods> specification);
    
    @Query("select g from Goods g where g.brandId = :brandId")
    List<Goods> findByBrand(@Param("brandId")long brandId);
    
    @Modifying
    @Query("update Goods b  set b.goodsStore = :goodsStore where b.id =:id")
    void updateGoodsByGoodsSort(@Param("goodsStore") Long goodsStore,@Param("id") Long id);
    
    
    @Query("select g from Goods g where g.deleted = false and g.displayed = true and g.brandId = ?1")
    public List<Goods> findbyBrand4Portal(long brandId);

    /************************************商品详情页---开始*************************************************/

    @Query("select g from Goods g where g.deleted = false and g.displayed = true and g.id = ?1")
    public Goods findGoodById(long id);

    @Query("select g from Goods g where g.deleted = false and g.displayed = true and g.brandId = ?1 order by g.virtualSales desc")
    Page<Goods> findSalesTopNByBrandId(long brandId,Pageable pageable);

    @Query("select g from Goods g where g.deleted = false and g.displayed = true and g.brandId = ?1 order by g.createdDate desc")
    Page<Goods> findNewTopNByBrandId(long brandId,Pageable pageable);

    @Query("select g from Goods g where g.deleted = false and g.displayed = true and g.categoryId = ?1 order by g.virtualSales desc")
    Page<Goods> findSalesTopNByCategoryId(long categoryId,Pageable pageable);

    @Query("select g from Goods g where g.deleted = false and g.displayed = true and g.categoryId = ?1 order by g.createdDate desc")
    Page<Goods> findNewTopNByCategoryId(long categoryId,Pageable pageable);

    /************************************商品详情页---结束************************************************/
    
    /**
     * 首页  lxx
     */
    @Query("select g from Goods g where g.deleted = false and g.displayed = true and g.id = :id")
    public Goods findGoodByIdIndex(long id);
    
    /**
     * 根据“加入推荐”里面的条件：精品，新品，热销
     * @param id
     * @return  (categoryId,limit,sort,orderBy,other));
     */
    @Query("select g from Goods g where g.categoryId=?1 and g.selectRecommends=?2 and g.deleted=false and g.displayed = true order by ?3, ?4")
	List<Goods> findGoodsByCondition(Long categoryId  , String selectRecommends , String sort , String orderBy);
    
    //***********前台分类界面  START**************//
    @Query("select count(*) from Goods g where g.deleted = false and g.displayed = true and g.categoryId in :ids")
	 long  getNumByCatIds(@Param("ids")Collection<Long> categoryIds);
    
    @Query("select g from Goods g where g.deleted = false and g.displayed = true order by g.virtualSales+g.realSales desc")
    Page<Goods> findSalesTopN(Pageable pageable);
    
    @Query("select g from Goods g where g.deleted = false and g.displayed = true order by g.createdDate desc")
    Page<Goods> findNewTopN(Pageable pageable);
    
    @Query("select g.id from Goods g where g.deleted = false and g.displayed = true and brandId = :brand and g.id in :ids")
    Set<Long> filterGoodsIdByBrand(@Param("brand")long brand,@Param("ids")Collection<Long> beFilteredGoodsId);
    
    @Query(value="SELECT * FROM (SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN (:ids) ) t WHERE t.price >= :minPrice AND t.price <= :maxPrice ORDER BY t.price desc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceOrderByPriceDesc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size);
    
    @Query(value="SELECT * FROM (SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN (:ids) ) t WHERE t.price >= :minPrice AND t.price <= :maxPrice ORDER BY :order  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPrice(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("order")String order,@Param("start")long start,@Param("size")long size);
    
    @Query(value="SELECT * FROM (SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN (:ids) ) t WHERE t.price >= :minPrice AND t.price <= :maxPrice ORDER BY t.price asc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceOrderByPriceAsc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size);
    
    @Query(value="SELECT * FROM (SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN (:ids) ) t WHERE t.price >= :minPrice AND t.price <= :maxPrice ORDER BY t.virtualSales desc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceOrderBySalesDesc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size);
    
    @Query(value="SELECT * FROM (SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN (:ids) ) t WHERE t.price >= :minPrice AND t.price <= :maxPrice ORDER BY t.virtualSales asc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceOrderBySalesAsc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size);
    
    @Query(value="SELECT * FROM (SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN (:ids) ) t WHERE t.price >= :minPrice AND t.price <= :maxPrice ORDER BY t.createdDate desc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceOrderByCreatedDateDesc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size);
    
    @Query(value="SELECT * FROM (SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN (:ids) ) t WHERE t.price >= :minPrice AND t.price <= :maxPrice ORDER BY t.createdDate asc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceOrderByCreatedDateAsc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size);
    
    @Query(value="SELECT t.id from (SELECT g.id ,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN (:ids) ) t where  t.price >= :minPrice AND t.price <= :maxPrice" ,nativeQuery=true)
    List<Long> findGoodsIdBetweenPrice4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice);
    
    
    @Query(value="SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE (s.user_rank = :rank OR s.user_rank like concat(:rank,',%') OR s.user_rank like concat('%,',:rank,',%') OR s.user_rank like concat('%,',:rank)) AND s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN :ids GROUP BY id HAVING price >= :minPrice AND price <= :maxPrice ORDER BY price desc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceFilterUserRankOrderByPriceDesc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size,@Param("rank")String user_rank);
    
    @Query(value="SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE (s.user_rank = :rank OR s.user_rank like concat(:rank,',%') OR s.user_rank like concat('%,',:rank,',%') OR s.user_rank like concat('%,',:rank)) AND s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN :ids GROUP BY id HAVING price >= :minPrice AND price <= :maxPrice ORDER BY price asc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceFilterUserRankOrderByPriceAsc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size,@Param("rank")String user_rank);
    
    @Query(value="SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE (s.user_rank = :rank OR s.user_rank like concat(:rank,',%') OR s.user_rank like concat('%,',:rank,',%') OR s.user_rank like concat('%,',:rank)) AND s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN :ids GROUP BY id HAVING price >= :minPrice AND price <= :maxPrice ORDER BY virtualSales desc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceFilterUserRankOrderBySalesDesc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size,@Param("rank")String user_rank);
    
    @Query(value="SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE (s.user_rank = :rank OR s.user_rank like concat(:rank,',%') OR s.user_rank like concat('%,',:rank,',%') OR s.user_rank like concat('%,',:rank)) AND s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN :ids GROUP BY id HAVING price >= :minPrice AND price <= :maxPrice ORDER BY virtualSales asc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceFilterUserRankOrderBySalesAsc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size,@Param("rank")String user_rank);
    
    @Query(value="SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE (s.user_rank = :rank OR s.user_rank like concat(:rank,',%') OR s.user_rank like concat('%,',:rank,',%') OR s.user_rank like concat('%,',:rank)) AND s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN :ids GROUP BY id HAVING price >= :minPrice AND price <= :maxPrice ORDER BY createdDate desc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceFilterUserRankOrderByCreatedDateDesc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size,@Param("rank")String user_rank);
    
    @Query(value="SELECT g.id,g.name,g.market_price,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price ,g.real_sales as realSales,g.virtual_sales as virtualSales,g.created_date as createdDate,g.goods_pic ,g.thumb_goods_pic FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE (s.user_rank = :rank OR s.user_rank like concat(:rank,',%') OR s.user_rank like concat('%,',:rank,',%') OR s.user_rank like concat('%,',:rank)) AND s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN :ids GROUP BY id HAVING price >= :minPrice AND price <= :maxPrice ORDER BY createdDate asc  limit :start , :size" ,nativeQuery=true)
    List<Object[]> findGoodsBetweenPriceFilterUserRankOrderByCreatedDateAsc4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("start")long start,@Param("size")long size,@Param("rank")String user_rank);
    
    @Query(value="SELECT t.id from (SELECT g.id ,CASE WHEN p.goods_price IS NOT NULL AND p.goods_price < g.shop_price THEN p.goods_price ELSE g.shop_price END AS price FROM goods  g LEFT OUTER JOIN (SELECT * FROM (SELECT spg.goods_id,spg.goods_price,sp.created_date FROM sales_promotion_goods spg LEFT OUTER JOIN sales_promotion sp ON spg.sales_promotion_id = sp.id WHERE spg.sales_promotion_id IN (SELECT s.id FROM sales_promotion s WHERE (s.user_rank = :rank OR s.user_rank like concat(:rank,',%') OR s.user_rank like concat('%,',:rank,',%') OR s.user_rank like concat('%,',:rank)) AND s.begin_time <=SYSDATE() AND s.end_time >= SYSDATE()) ORDER BY goods_id DESC,created_date DESC) temp_spg  GROUP BY goods_id) p ON g.id = p.goods_id WHERE g.id IN :ids GROUP BY id HAVING price >= :minPrice AND price <= :maxPrice) t" ,nativeQuery=true)
    List<Long> findGoodsIdBetweenPriceFilterUserRank4Portal(@Param("ids")Collection<Long> ids ,@Param("minPrice")BigDecimal minPrice,@Param("maxPrice")BigDecimal maxPrice,@Param("rank")String user_rank);
    
  //***********前台分类界面 END**************//
}
